package com.eleadmin.order.biz.impl;

import com.eleadmin.damon.utils.constant.UserConstant;
import com.eleadmin.damon.utils.results.BaseDao;
import com.eleadmin.damon.utils.results.PageResult2;
import com.eleadmin.damon.utils.utils.Utils;
import com.eleadmin.common.system.entity.User;
import com.eleadmin.order.biz.CustomerBiz33;
import com.eleadmin.order.entity.Customer23123;
import com.eleadmin.common.core.web.JsonResult;
import com.eleadmin.order.result.InvoiceResult;
import com.eleadmin.order.result.SelectResult;
import org.apache.poi.ss.formula.functions.T;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.Map;
import java.util.List;

/**
 * 数据层实现类
 *
 * @author : Doman
 * @version ：1.0
 * @date : 2023-02-13 15:50:11
 */
@Repository
public class CustomerBizImpl222 extends BaseDao implements CustomerBiz33 {

    @Override
    public JsonResult list(PageResult2<T> page, Customer23123 customer, User loginUser) throws Exception {
        Map<String, String> map = new HashMap<String, String>();
        RowMapper<Customer23123> rm = new BeanPropertyRowMapper<Customer23123>(Customer23123.class);
        StringBuffer sql = new StringBuffer("select c.id,c.organization_id,c.name,suser3.username account");
        sql.append(",c.abbreviation,c.profile_picture,c.pinyin");
        sql.append(",c.email,c.social_code,c.business_license");
        sql.append(",c.road_transport_permit,c.agreement,c.agreement_start,c.agreement_end");
        sql.append(",c.settle,c.contacts,c.contacts_phone,c.salesmanId");
        sql.append(",c.salesman,c.operaterId,c.operater,c.address");
        sql.append(",c.bank_account,c.bank,c.bank_tel");
        sql.append(",c.invoice_state,c.type,c.state");
        sql.append(",c.message,c.gzh_send,c.offer_limit_num,c.order_num");
        sql.append(",c.create_time,c.create_user,c.update_time,c.update_user");
        sql.append(",sogzn.organization_name organizationName,suser.nickname createName,suser2.nickname updateName ");
        sql.append(" from chf_customer c ");
        sql.append(" inner join sys_user suser on suser.user_id=c.create_user ");
        sql.append(" left join sys_user suser2 on suser2.user_id=c.update_user ");
        sql.append(" left join sys_user suser3 on suser3.customer_id=c.id ");
        if (Utils.isNotEmpty(customer.getType())) {
            if (customer.getType().equals("货代外贸")) {
                sql.append(" and (suser3.type='货代' or suser3.type='外贸') ");
            } else {
                sql.append(" and suser3.type=:type ");
            }
        }
        sql.append(" left join chf_contain_ocean_apply cc on cc.apply_id=c.id ");
        map.put("type", customer.getType());
        sql.append(" inner join sys_organization sogzn on sogzn.organization_id=c.organization_id and sogzn.deleted=0 ");
        sql.append(" where c.deleted=0 ");
        if (loginUser.getUserId() != UserConstant.ADMIN_ID) {
            sql.append(" and c.organization_id=:tableOrganizationId ");
            map.put("tableOrganizationId", loginUser.getOrganizationId() + "");
        }
        if (Utils.isNotEmpty(customer)) {
            if (Utils.isNotEmpty(customer.getName())) {
                sql.append(" and (instr(c.name,:name)>0 or instr(c.pinyin, :name)>0) ");
                map.put("name", customer.getName());
            }
            if (Utils.isNotEmpty(customer.getState())) {
                if (customer.getState().equals("未关联")) {
                    sql.append(" and  cc.apply_id is null  ");
                } else {
                    sql.append(" and c.state=:state ");
                    map.put("state", customer.getState());
                }
            }
            if (Utils.isNotEmpty(customer.getType())) {
                if (customer.getType().equals("货代外贸")) {
                    sql.append(" and (c.type='货代' or c.type='外贸') ");
                } else {
                    sql.append(" and c.type=:type ");
                }
            }
            if (Utils.isNotEmpty(customer.getSort()) && Utils.isNotEmpty(customer.getOrder())) {
                sql.append(" order by c." + customer.getSort() + " " + customer.getOrder());
            } else {
                sql.append(" order by c.create_time desc");
            }
        }
        return jsonResult(getPageQueryList(page, sql.toString(), map, rm));
    }

    @Override
    public List<Customer23123> list(Customer23123 customer, User loginUser) {
        Map<String, String> map = new HashMap<String, String>();
        RowMapper<Customer23123> rm = new BeanPropertyRowMapper<Customer23123>(Customer23123.class);
        StringBuffer sql = new StringBuffer("select c.id,c.organization_id,c.name,suser3.username account");
        sql.append(",c.abbreviation,c.profile_picture,c.pinyin");
        sql.append(",c.email,c.social_code,c.business_license");
        sql.append(",c.road_transport_permit,c.agreement,c.agreement_start,c.agreement_end");
        sql.append(",c.settle,c.contacts,c.contacts_phone,c.salesmanId");
        sql.append(",c.salesman,c.operaterId,c.operater,c.address");
        sql.append(",c.bank_account,c.bank,c.bank_tel");
        sql.append(",c.invoice_state,c.type,c.state");
        sql.append(",c.message,c.gzh_send,c.offer_limit_num,c.order_num");
        sql.append(",c.create_time,c.create_user,c.update_time,c.update_user");
        sql.append(",sogzn.organization_name organizationName,suser.nickname createName ");
        sql.append(" from chf_customer c ");
        sql.append(" inner join sys_user suser on suser.user_id=c.create_user ");
        sql.append(" left join sys_user suser3 on suser3.customer_id=c.id ");
        if (Utils.isNotEmpty(customer.getType())) {
            if (customer.getType().equals("货代外贸")) {
                sql.append(" and (suser3.type='货代' or suser3.type='外贸') ");
            } else {
                sql.append(" and suser3.type=:type ");
            }
        }
        map.put("type", customer.getType());
        sql.append(" inner join sys_organization sogzn on sogzn.organization_id=c.organization_id and sogzn.deleted=0 ");
        sql.append(" where c.deleted=0 ");
        if (loginUser.getUserId() != UserConstant.ADMIN_ID) {
            sql.append(" and c.organization_id=:tableOrganizationId ");
            map.put("tableOrganizationId", loginUser.getOrganizationId() + "");
        }
        if (Utils.isNotEmpty(customer)) {
            if (Utils.isNotEmpty(customer.getName())) {
                sql.append(" and (instr(c.name,:name)>0 or instr(c.pinyin, :name)>0) ");
                map.put("name", customer.getName());
            }
            if (Utils.isNotEmpty(customer.getState())) {
                sql.append(" and instr(c.state,:state)>0 ");
                map.put("state", customer.getState());
            }
            if (Utils.isNotEmpty(customer.getType())) {
                if (customer.getType().equals("货代外贸")) {
                    sql.append(" and (c.type='货代' or c.type='外贸') ");
                } else {
                    sql.append(" and c.type=:type ");
                }
            }
            if (Utils.isNotEmpty(customer.getSort()) && Utils.isNotEmpty(customer.getOrder())) {
                sql.append(" order by c." + customer.getSort() + " " + customer.getOrder());
            } else {
                sql.append(" order by c.create_time asc");
            }
        }
        getMapParameter(sql, map);
        return jdbcTemplate.query(sql.toString(), map, rm);
    }

    @Override
    public List<SelectResult> listSel(Customer23123 customer, User loginUser) {
        Map<String, String> map = new HashMap<String, String>();
        RowMapper<SelectResult> rm = new BeanPropertyRowMapper<SelectResult>(SelectResult.class);
        StringBuffer sql = new StringBuffer("select c.id value, c.name label,  c.invoice_state, c.abbreviation");
        sql.append(" from chf_customer c ");
        sql.append(" where c.deleted=0 and c.state='已审核' ");
        if (loginUser.getUserId() != UserConstant.ADMIN_ID) {
            sql.append(" and c.organization_id=:tableOrganizationId ");
            map.put("tableOrganizationId", loginUser.getOrganizationId() + "");
        }
        if (Utils.isNotEmpty(customer)) {
            if (Utils.isNotEmpty(customer.getName())) {
                sql.append(" and (instr(c.name,:name)>0 or instr(c.pinyin, :name)>0 ) ");
                map.put("name", customer.getName());
            }
            if (Utils.isNotEmpty(customer.getKeywords()) && customer.getKeywords().equals("salesman")) {
                sql.append(" and c.salesmanId=:salesmanId ");
                map.put("salesmanId", loginUser.getUserId()+"");
            }
            if (Utils.isNotEmpty(customer.getType())) {
                if (customer.getType().equals("货代外贸")) {
                    sql.append(" and (c.type='货代' or c.type='外贸') ");
                } else {
                    sql.append(" and c.type=:type ");
                }
                map.put("type", customer.getType());
            }
        }

        getMapParameter(sql, map);
        return jdbcTemplate.query(sql.toString(), map, rm);
    }

    @Override
    public List<InvoiceResult> invoiceSel(Customer23123 customer, User loginUser) {
        Map<String, String> map = new HashMap<String, String>();
        RowMapper<InvoiceResult> rm = new BeanPropertyRowMapper<InvoiceResult>(InvoiceResult.class);
        StringBuffer sql = new StringBuffer("select c.id value, c.name, c.abbreviation label,c.invoice_state invoiceState, c.type");
        sql.append(" from chf_customer c ");
        sql.append(" where c.deleted=0");
        if (loginUser.getUserId() != UserConstant.ADMIN_ID) {
            sql.append(" and c.organization_id=:tableOrganizationId ");
            map.put("tableOrganizationId", loginUser.getOrganizationId() + "");
        }
        if (Utils.isNotEmpty(customer)) {
            if (Utils.isNotEmpty(customer.getName())) {
                sql.append(" and (instr(c.name,:name)>0 or instr(c.pinyin, :name)>0) ");
                map.put("name", customer.getName());
            }
        }
        sql.append(" union all ");
        sql.append(" select d.id value, c2.name, d.name label, '0' invoiceState, '司机' type ");
        sql.append(" from chf_driver d ");
        sql.append(" inner join chf_customer c2 on c2.id=d.car_team_id   ");
        sql.append(" where d.deleted=0 and c2.name='甩手掌柜宁波车队' ");
        sql.append(" limit 300 ");
        getMapParameter(sql, map);
        return jdbcTemplate.query(sql.toString(), map, rm);
    }

    @Override
    public Customer23123 getById(String id) {
        RowMapper<Customer23123> rm = new BeanPropertyRowMapper<Customer23123>(Customer23123.class);
        Map<String, String> map = new HashMap<String, String>();
        StringBuffer sql = new StringBuffer("select c.id,c.organization_id,c.name");
        sql.append(",c.abbreviation,c.profile_picture,c.pinyin");
        sql.append(",c.email,c.social_code,c.business_license");
        sql.append(",c.road_transport_permit,c.agreement,c.agreement_start,c.agreement_end");
        sql.append(",c.settle,c.contacts,c.contacts_phone,c.salesmanId");
        sql.append(",c.salesman,c.operaterId,c.operater,c.address");
        sql.append(",c.bank_account,c.bank,c.bank_tel");
        sql.append(",c.invoice_state,c.type,c.state");
        sql.append(",c.message,c.gzh_send,c.offer_limit_num,c.order_num");
        sql.append(",c.create_time,c.create_user,c.update_time,c.update_user");
        sql.append(",sogzn.organization_name organizationName,suser.nickname createName ");
        sql.append(" from chf_customer c ");
        sql.append(" inner join sys_user suser on suser.user_id=c.create_user ");
        sql.append(" inner join sys_organization sogzn on sogzn.organization_id=c.organization_id and sogzn.deleted=0 ");
        sql.append(" where c.deleted=0 and c.id=:id ");
        map.put("id", id);
        getMapParameter(sql, map);
        return jdbcTemplate.queryForObject(sql.toString(), map, rm);
    }

    @Override
    public Integer save(Customer23123 customer, User loginUser) {
        GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();
        KeyHolder keyHolder = new GeneratedKeyHolder();
        Map<String, String> map = new HashMap<String, String>();
        StringBuffer sql = new StringBuffer("insert into chf_customer (");
        sql.append("organization_id,name,abbreviation,profile_picture");
        sql.append(",pinyin,email");
        sql.append(",social_code,business_license,road_transport_permit,agreement");
        sql.append(",agreement_start,agreement_end,settle,contacts");
        sql.append(",contacts_phone,salesmanId,salesman,operaterId");
        sql.append(",operater,address,invoice_state");
        sql.append(",bank_account,bank,bank_tel");
        sql.append(",type,state,message,gzh_send");
        sql.append(",offer_limit_num,order_num,create_user,create_time) values ( ");
        sql.append(":organization_id,:name,:abbreviation,:profile_picture");
        sql.append(",:pinyin,:email");
        sql.append(",:social_code,:business_license,:road_transport_permit,:agreement");
        sql.append(",:agreement_start,:agreement_end,:settle,:contacts");
        sql.append(",:contacts_phone,:salesmanId,:salesman,:operaterId");
        sql.append(",:operater,:address,:invoice_state");
        sql.append(",:bank_account,:bank,:bank_tel");
        sql.append(",:type,:state,:message,:gzh_send");
        sql.append(",:offer_limit_num,:order_num,:create_user,now())");
        map.put("organization_id", loginUser.getOrganizationId() + "");
        map.put("name", customer.getName());
        map.put("abbreviation", customer.getAbbreviation());
        map.put("profile_picture", customer.getProfilePicture());
        map.put("pinyin", customer.getPinyin());
        map.put("email", customer.getEmail());
        map.put("social_code", customer.getSocialCode());
        map.put("business_license", customer.getBusinessLicense());
        map.put("road_transport_permit", customer.getRoadTransportPermit());
        map.put("agreement", customer.getAgreement());
        map.put("agreement_start", customer.getAgreementStart());
        map.put("agreement_end", customer.getAgreementEnd());
        map.put("settle", customer.getSettle());
        map.put("contacts", customer.getContacts());
        map.put("contacts_phone", customer.getContactsPhone());
        map.put("salesmanId", customer.getSalesmanid() == null ? null : customer.getSalesmanid() + "");
        map.put("salesman", customer.getSalesman());
        map.put("operaterId", customer.getOperaterid() == null ? null : customer.getOperaterid() + "");
        map.put("operater", customer.getOperater());
        map.put("address", customer.getAddress());
        map.put("invoice_state", customer.getInvoiceState() == null ? null : customer.getInvoiceState() + "");
        map.put("bank_account", customer.getBankAccount());
        map.put("bank", customer.getBank());
        map.put("bank_tel", customer.getBankTel());
        map.put("type", customer.getType());
        map.put("state", customer.getState());
        map.put("message", customer.getMessage() == null ? null : customer.getMessage() + "");
        map.put("gzh_send", customer.getGzhSend() == null ? null : customer.getGzhSend() + "");
        map.put("offer_limit_num", customer.getOfferLimitNum() == null ? null : customer.getOfferLimitNum() + "");
        map.put("order_num", customer.getOrderNum() == null ? null : customer.getOrderNum() + "");
        map.put("create_user", loginUser.getUserId() + "");
        getMapParameter(sql, map);
        int retVal = this.jdbcTemplate.update(sql.toString(), new SqlParameterSource() {
            @Override
            public boolean hasValue(String paramName) {
                return map.containsKey(paramName);
            }

            @Override
            public Object getValue(String paramName) throws IllegalArgumentException {
                return map.get(paramName);
            }
        }, generatedKeyHolder);

        Integer id = generatedKeyHolder.getKey().intValue();
        return id;
    }

    @Override
    public boolean examCustomer(Customer23123 customer, User loginUser) {
        Map<String, String> map = new HashMap<String, String>();
        StringBuffer sql = new StringBuffer("update chf_customer set ");
        sql.append("state=:state,update_time=now() ");
        sql.append(",update_user=:update_user where id=:id ");
        map.put("state", customer.getState());
        map.put("update_user", loginUser.getUserId() + "");
        map.put("id", customer.getId());
        getMapParameter(sql, map);
        int count = jdbcTemplate.update(sql.toString(), map);
        return count > 0;
    }

    @Override
    public boolean update(Customer23123 customer, User loginUser) {
        Map<String, String> map = new HashMap<String, String>();
        StringBuffer sql = new StringBuffer("update chf_customer set ");
        sql.append("name=:name,abbreviation=:abbreviation,profile_picture=:profile_picture ");
        sql.append(",pinyin=:pinyin ");
        sql.append(",email=:email,social_code=:social_code,business_license=:business_license ");
        sql.append(",road_transport_permit=:road_transport_permit,agreement=:agreement,agreement_start=:agreement_start ");
        sql.append(",agreement_end=:agreement_end,settle=:settle,contacts=:contacts ");
        sql.append(",contacts_phone=:contacts_phone,salesmanId=:salesmanId,salesman=:salesman ");
        sql.append(",operaterId=:operaterId,operater=:operater,address=:address ");
        sql.append(",bank_account=:bank_account,bank=:bank,bank_tel=:bank_tel");
        sql.append(",invoice_state=:invoice_state,type=:type ");
        sql.append(",state=:state,message=:message,gzh_send=:gzh_send ");
        sql.append(",offer_limit_num=:offer_limit_num,order_num=:order_num,update_time=now() ");
        sql.append(",update_user=:update_user where id=:id ");
        map.put("name", customer.getName());
        map.put("abbreviation", customer.getAbbreviation());
        map.put("profile_picture", customer.getProfilePicture());
        map.put("pinyin", customer.getPinyin());
        map.put("email", customer.getEmail());
        map.put("social_code", customer.getSocialCode());
        map.put("business_license", customer.getBusinessLicense());
        map.put("road_transport_permit", customer.getRoadTransportPermit());
        map.put("agreement", customer.getAgreement());
        map.put("agreement_start", customer.getAgreementStart());
        map.put("agreement_end", customer.getAgreementEnd());
        map.put("settle", customer.getSettle());
        map.put("contacts", customer.getContacts());
        map.put("contacts_phone", customer.getContactsPhone());
        map.put("salesmanId", customer.getSalesmanid() == null ? null : customer.getSalesmanid() + "");
        map.put("salesman", customer.getSalesman());
        map.put("operaterId", customer.getOperaterid() == null ? null : customer.getOperaterid() + "");
        map.put("operater", customer.getOperater());
        map.put("address", customer.getAddress());
        map.put("invoice_state", customer.getInvoiceState() == null ? null : customer.getInvoiceState() + "");
        map.put("bank_account", customer.getBankAccount());
        map.put("bank", customer.getBank());
        map.put("bank_tel", customer.getBankTel());
        map.put("type", customer.getType());
        map.put("state", customer.getState());
        map.put("message", customer.getMessage() == null ? null : customer.getMessage() + "");
        map.put("gzh_send", customer.getGzhSend() == null ? null : customer.getGzhSend() + "");
        map.put("offer_limit_num", customer.getOfferLimitNum() == null ? null : customer.getOfferLimitNum() + "");
        map.put("order_num", customer.getOrderNum() == null ? null : customer.getOrderNum() + "");
        map.put("update_user", loginUser.getUserId() + "");
        map.put("id", customer.getId());
        getMapParameter(sql, map);
        int count = jdbcTemplate.update(sql.toString(), map);
        return count > 0;
    }

    @Override
    public boolean remove(String id, User loginUser) {
        int count = 0;
        Map<String, String> map = null;
        if (id.length() > 0) {
            for (String id1 : id.split(",")) {
                map = new HashMap<String, String>();
                if (id1 == null || id1.equals("")) {
                    break;
                }
                StringBuffer sql = new StringBuffer("update chf_customer set deleted=1" +
                        ",update_user=:update_user, update_time=now() "
                        + " where id =:id ");
                map.put("update_user", loginUser.getUserId() + "");
                map.put("id", id1);
                getMapParameter(sql, map);
                count = jdbcTemplate.update(sql.toString(), map);
            }
        }
        return count > 0;
    }
}
